package com.ty.dao.impl;

import com.ty.bean.Student;
import com.ty.dao.DBUtils;
import com.ty.dao.StudentDao;
import com.ty.util.StudentEnum;

import java.sql.Array;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author 田野
 */
public class StudentDaoImpl extends DBUtils implements StudentDao {
    @Override
    public List<Student> getStudents(String stuname, String stuno, int sex, int pageIndex, int pageSize) {
        ArrayList<Student> students = new ArrayList<>();
        List params = new ArrayList();

        try {
            // String sql = "select * from student";
            // 这里非常关键的是 state=-1 表示的是删除的数据，但是是个假删除，就可以加一个限定的条件  还有total()方法
            StringBuffer sqlBuf = new StringBuffer(" select * from student where 1=1 and state!=-1 ");
            if (stuname != null && stuname.length()>0){
                sqlBuf.append(" and stuname like ? ");
                params.add("%"+stuname+"%");
            }

            if (stuno != null && stuno.length()>0){
                sqlBuf.append(" and stuno = ? ");
                params.add(stuno);
            }

            if (sex != -1){
                sqlBuf.append(" and sex = ? ");
                params.add(sex);
            }

            // 加上分页条件  limit  (pageIndex-1)*pageSize,pageSize  limit 0,5
            sqlBuf.append(" limit ?,? ");
            params.add((pageIndex-1)*pageSize);
            params.add(pageSize);


            resultSet = query(sqlBuf.toString(), params);
            if (resultSet == null){
                return null;
            }

            while (resultSet.next()){
                Student student = new Student();

                student.setStuId(resultSet.getInt("stuid"));
                student.setStuNo(resultSet.getString("stuno"));
                student.setStuName(resultSet.getString("stuname"));
                student.setSex(resultSet.getInt("sex"));
                student.setPhone(resultSet.getString("phone"));
                student.setProfession(resultSet.getString("profession"));
                student.setRegDate(resultSet.getDate("regdate"));
                // 可以补全所有的列
                // 添加到list集合中
                students.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return students;
    }

    @Override
    public int total(String stuname, String stuno, int sex) {
        int total = 0;
        try {
            List params = new ArrayList();
            // 这里的-1表示删除的状态，假删除  还有getStudents()方法需要限定
            StringBuffer sqlBuf = new StringBuffer(" select count(*) from student where 1=1 and state!= -1");
            if (stuname != null && stuname.length() > 0) {
                sqlBuf.append(" and stuname like ? ");
                params.add("%" + stuname + "%");
            }

            if (stuno != null && stuno.length() > 0) {
                sqlBuf.append(" and stuno = ? ");
                params.add(stuno);
            }

            if (sex != -1) {
                sqlBuf.append(" and sex = ? ");
                params.add(sex);
            }

            resultSet = query(sqlBuf.toString(), params);
            while (resultSet.next()){
                total = resultSet.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }

        return total;

    }

    @Override
    public int insertStu(Student student) {
        int update = 0;
        try {
            String sql = " insert into student values(null, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ";

            List params = new ArrayList();
            params.add(student.getStuName());
            params.add(student.getStuNo());
            params.add(student.getSex());
            params.add(student.getPhone());
            params.add(student.getEmail());
            params.add(student.getRegistered());
            params.add(student.getAddress());
            params.add(student.getProfession());
            params.add(student.getIdNumber());
            params.add(student.getPolitics());
            params.add(new Date());
            // 1表示在读 0 表示休学状态  -2 表示退学  -1 表示删除
            params.add(StudentEnum.READING.type);
            params.add(student.getIntroduction());
            params.add(student.getGid());

            update = update(sql, params);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }


        return update;
    }

    @Override
    public Student findById(int sid) {
        Student student = new Student();
        List params = new ArrayList();
        try {
            String sql = " select * from student where stuid = ?";
            params.add(sid);
            resultSet = query(sql, params);

            while (resultSet.next()){
                student.setStuId(resultSet.getInt("stuid"));
                student.setStuName(resultSet.getString("stuname"));
                student.setStuNo(resultSet.getString("stuno"));
                student.setSex(resultSet.getInt("sex"));
                student.setPhone(resultSet.getString("phone"));
                student.setEmail(resultSet.getString("email"));
                student.setRegistered(resultSet.getString("registered"));
                student.setAddress(resultSet.getString("address"));
                student.setProfession(resultSet.getString("profession"));
                student.setIdNumber(resultSet.getString("idnumber"));
                student.setPolitics(resultSet.getString("politics"));
                student.setRegDate(resultSet.getDate("regdate"));
                student.setState(resultSet.getInt("state"));
                student.setIntroduction(resultSet.getString("introduction"));
                student.setGid(resultSet.getInt("gid"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }

        return student;
    }

    @Override
    public int updateStu(Student student) {
        // 完善全部非主键列以外的列的更新
        int update = 0;
        try {
            String sql = " update student set stuname=?, sex=?, phone=?, " +
                    "email=?, registered=?, address=?, profession=?, idnumber=?, politics=?, " +
                    " introduction=?, gid=? where stuid=?";
            List params = new ArrayList();
            params.add(student.getStuName());
            params.add(student.getSex());
            params.add(student.getPhone());
            params.add(student.getEmail());
            params.add(student.getRegistered());
            params.add(student.getAddress());
            params.add(student.getProfession());
            params.add(student.getIdNumber());
            params.add(student.getPolitics());
            params.add(student.getIntroduction());
            params.add(student.getGid());
            params.add(student.getStuId());

            update = update(sql, params);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }

        return update;
    }

    @Override
    public int delStu(String sid) {
        // 假删除：更新表中的状态列的值
        // 真删除：直接delete
        int update = 1;
        try {
            String sql = " update student set state=? where stuid=?";

            List params = new ArrayList();

            params.add(StudentEnum.DELETE.type);
            params.add(sid);
            update = update(sql, params);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }

        return update;
    }

    @Override
    public Student findByStuNo(String stuNo) {
        List params = new ArrayList();
        params.add(stuNo);
        Student student = null;

        try {
            String sql = " select * from student where stuNo=? ";
            resultSet = query(sql, params);
            while (resultSet.next()){
                student = new Student();
                student.setStuId(resultSet.getInt("stuid"));
                student.setStuName(resultSet.getString("stuname"));
                student.setStuNo(resultSet.getString("stuno"));
                student.setSex(resultSet.getInt("sex"));
                student.setPhone(resultSet.getString("phone"));
                student.setEmail(resultSet.getString("email"));
                student.setRegistered(resultSet.getString("registered"));
                student.setAddress(resultSet.getString("address"));
                student.setProfession(resultSet.getString("profession"));
                student.setIdNumber(resultSet.getString("idnumber"));
                student.setPolitics(resultSet.getString("politics"));
                student.setRegDate(resultSet.getDate("regdate"));
                student.setState(resultSet.getInt("state"));
                student.setIntroduction(resultSet.getString("introduction"));
                student.setGid(resultSet.getInt("gid"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }

        return student;
    }
}
